<?php

namespace app\checkbill\logic;

use app\admin\logic\BasicLogic;
use app\checkbill\model\Qqwyskuweight;
use app\common\library\RedissLock;
use app\common\service\CommonSystemImportService;
use app\data\util\MqQueueName;
use app\systems\logic\MqTaskPushLogic;
use think\facade\Db;
use think\facade\Cache;
use app\shop\logic\DailyEstimateLogic;
use app\common\library\Excel;

class BillQcLogic  extends BasicLogic
{

    /**
     * @desc 全程无忧3月账单对账
     * @author  luckyXiong
     */
    public function import_qc_detail($params){
        try{
            $result = CommonSystemImportService::staticModel()->operate_import_data([
                    $this,//当前对应的service
                    'save',//类型: template 下载模板; save :保存 导入
                    CommonSystemImportService::TYPE_IMPORT_QC_PAGE,
                    1,
                    $params
                ]
            );
        }catch (\Exception $e) {
            return $this->jsonMessage(201, false, $e->getMessage(), null);
        }
    }



    /**
     * @desc 将导入数据写入目标表
     * @author xx
     * @date 2024-03-21
     */
    public function import_qc_info($params=[]){
        $key        = 'import_qc_info:'.time();
        $redis_lock = new RedissLock();
        $lock       = $redis_lock::lock($key, $redis_lock::UNBLOCK, 500);
        if(!$lock){
            return ['code'=>201,'msg'=>'已处理过'];
        }
        $number             = getIsset($params,'number');
        //数据入库逻辑
        $info = [
            'number'        =>$number
        ];
        $res = Db::name('gettrackinfo')->insertGetId($info);
        $lock->unlock();
        $info['status'] = $res == 1 ? 1:2;
        return  $info;
    }


    /**
     * @param $row
     * @return string[]
     * @desc 数据title 导入组装数据结构
     */
    public function get_excel_row_data($row){
        return [
            'number'  => empty($row['A'])? '': trim($row['A']),
        ];
    }


    /**
     * @desc  获取月度预估值
     * @date  2024-04-24
     */
    public function getIndex($param=[]){
        $date          = getIsset($param,'date');
        $limit         = getIsset($param,'limit',10);
        $page          = getIsset($param,'page',1);
        $where         = [];
        if($date){
            $where[]=['date','=',$date];
        }
        $list = Db::name('exchange')
            ->where($where)
            ->field('id,date,currency,proportion,storage_fees,first_fees,platform_fee as platformFee,refund_fee,operate_time')
            ->order('date desc')
            ->limit(($page - 1) * $limit, $limit)
            ->select();
        $list = objectToArray($list);
        $count= Db::name('exchange')->where($where)->count();
        $data['list']  = $list;
        $data['total'] = $count;
        return $this->jsonMessage(200, true, '获取成功', $data);
    }



    /**
     * @desc  编辑获取月度预估值
     * @date  2024-04-24
     */
    public function updateFees($param=[]){
        $storage_fees  = getIsset($param,'storage_fees');
        $first_fees    = getIsset($param,'first_fees');
        $platformFee   = getIsset($param,'platformFee');
        $refund_fee    = getIsset($param,'refund_fee');
        $id            = getIsset($param,'id');
        $admin_id      = getIsset($param,'admin_id');
        htmlLog(['param'=>$param,'admin_id'=>$admin_id],OPERATE_LOG . date('Ym') . '/updateFees/' . date('Ymd') . '.txt');
        Db::name('exchange')->where('id',$id)->update(
            [
                'storage_fees' =>$storage_fees,
                'first_fees'   =>$first_fees,
                'platform_fee' =>$platformFee,
                'refund_fee'   =>$refund_fee,
                'operate_time' =>date("Y-m-d H:i:s", time()),
            ]
        );
        return $this->jsonMessage(200, true, '操作成功', null);
    }


    /**
     * @desc 获取店铺月度财务报表数据
     * @date 2024-04-24
     */
    public function getShopMonthIndex($params = []){
        $shop_id = $params['shop_id'] ?? "";
        $date    = $params['date'] ?? '';
        $limit   = $params['limit'] ?? '20';
        $data = $this->getShopMonthIndexMonth($date,$shop_id,$limit);
        return json(DefaultResponse(200, true, '获取成功', $data));
    }


    /**
     * @desc 前程无忧数据生产
     * @date 2024-04-25
     */
    public function checkQcProduce(){
        $list = Db::name('checkbill_qqwy')->field('id,reference_no,waybill_no,postal,total_weight,pieces,operating_fee')->select()->toArray();
        //数据推入队列处理;
        foreach($list as $v){
            MqTaskPushLogic::mq_task_push(
                '',
                MqQueueName::QUEUE_TIKTOK_QC_MONTH_ORDER_QUEUE,
                $v,
                false
            );
        }
    }



    /**
     * @desc 前程无忧数据消费
     * @date 2024-04-25
     */
    public function checkQcComsumer($checkbill_qqwy){

        $checkbill_qqwy['reference_no'] = trim($checkbill_qqwy['reference_no']);
        $checkbill_qqwy['postal']       = trim($checkbill_qqwy['postal']);
        $checkbill_qqwy['total_weight'] = trim($checkbill_qqwy['total_weight']);
        //分区
        $postal = (int)substr($checkbill_qqwy['postal'],0,3);
        $recon_qqwy_zone = Db::name('checkbill_qqwyzone')
            ->where('postal_code_start',"<=",$postal)
            ->where('postal_code_end',">=",$postal)
            ->find();
        if(!empty($recon_qqwy_zone)){
            $zone_id_new = $recon_qqwy_zone['zone_id'];
        }else{
            $zone_id_new = 0;
        }
        //总重量
        $total_weight_new = 0;
        //件数
        $pieces_new = 0;
        if(!empty($checkbill_qqwy['reference_no'])){
            $info = Db::name('gettrackskuinfo')
                ->alias('f')
                ->leftJoin('ba_gettrackorderinfo o', 'f.sku_id = o.sku_id')
                ->where('o.order_id', $checkbill_qqwy['reference_no'])
                ->field('f.sku_id, f.new_sku,f.count as qtyOrdered')
                ->select()->toArray();
            $outputArray = [];
            foreach ($info as $item) {
                $sku = $item['new_sku'];
                if (isset($outputArray[$sku])) {
                    $outputArray[$sku]['qtyOrdered'] += $item['qtyOrdered'];
                } else {
                    $outputArray[$sku] = $item;
                }
            }
            $OrderDetail3 = array_values($outputArray);
            if(!empty($OrderDetail3)){
                $skus = array_column($OrderDetail3,'new_sku');
                $weightInfo =  (new Qqwyskuweight())->where([
                    'new_sku' => $skus
                ])->field('new_sku,weight')->select()->toArray();
                $weightInfoMap = array_column($weightInfo,null,'new_sku');
                $info          = array_column($OrderDetail3,null,'new_sku');
                foreach($info as $v){
                    $weightValues       = $weightInfoMap[$v['new_sku']]??[];
                    $weight             = isset($weightValues['weight'])&&$weightValues['weight'] ? intval($weightValues['weight']*$v['qtyOrdered']) : 0;
                    $total_weight_new        +=$weight;
                    $pieces_new           +=$v['qtyOrdered'];
                }
            }
        }
        //运费
        $operating_fee_news = 0;
        $operating_fee_news2 = 0;
        if(!empty($total_weight_new)){
            //运费-程序计算
            $total_weight_new = (float)number_format($total_weight_new/1000, 2, '.', '');
            $recon_qqwy_zoneweight = Db::name('checkbill_qqwyzoneweight')
                ->where('weight_range_star',"<=",$total_weight_new)
                ->where('weight_range_end',">=",$total_weight_new)
                ->where('zone_id',">=",$zone_id_new)
                ->find();
            if(!empty($recon_qqwy_zoneweight)){
                $operating_fee_news = $recon_qqwy_zoneweight['freight_cost'];
            }
        }
        //运费-程序计算(使用账单的重量来计算)
        if(!empty($checkbill_qqwy['total_weight'])){
            $recon_qqwy_zoneweight2 = Db::name('checkbill_qqwyzoneweight')
                ->where('weight_range_star',"<=",$checkbill_qqwy['total_weight'])
                ->where('weight_range_end',">=",$checkbill_qqwy['total_weight'])
                ->where('zone_id',">=",$zone_id_new)
                ->find();
            if(!empty($recon_qqwy_zoneweight2)){
                $operating_fee_news2 = $recon_qqwy_zoneweight2['freight_cost'];
            }
        }

        $update['zone_id_new'] = $zone_id_new;
        $update['total_weight_new'] = $total_weight_new;
        $update['pieces_new'] = $pieces_new;
        $update['operating_fee_news'] = $operating_fee_news;
        $update['operating_fee_news2'] = $operating_fee_news2;
        $update['reconciliation_status'] = 1;
        Db::name('checkbill_qqwy')
            ->where('id',$checkbill_qqwy['id'])
            ->update($update);
        return true;
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 计算口径跟MonthlyGrossProfitStatistics方法不一样
     * @author zhangheng
     */
    public function getShopMonthIndexMonth($date,$shop_id,$limit)
    {
        $shop_id = $shop_id ?? "";
        $date    = $date ?? "";
        $where   = [];
        $thismonth = date('m');
        $thisyear  = date('Y');
        if (!empty($shop_id)) {
            $where[] = ['a.shop_id', '=', $shop_id];
        }
        if ($date) {
            $where[] = ['a.time', '=', strtotime($date)];
        }else{
            if($thismonth=='1'){
                $year       = $thisyear-1;
                $last_month = '12';
            }else{
                $year       = $thisyear;
                $last_month = $thismonth-1;
                $last_month = in_array($last_month,[10,11]) ? $last_month : '0'.$last_month;
            }
            //update 2024-05-22 默认显示上月
            $where[] = ['a.date', '=', $year.'-'.$last_month];
        }
        
        $list = Db::name('report_monthly_profit')
            ->field('a.*')
            ->alias('a')
            ->join('ba_shops b','b.id = a.shop_id', 'left')
            ->where($where)
            ->order("a.time desc,b.sort_id asc")
            ->paginate($limit);
        $lists = $list->items();

        $sum = Db::name('report_monthly_profit')
            ->field('
                sum(PanelSalesRevenue) as PanelSalesRevenue,
                sum(refund_subtotal) as refund_subtotal,
                sum(PlatformSettlementFee) as PlatformSettlementFee,
                sum(BrushSale) as BrushSale,
                sum(NetSalesRevenue) as NetSalesRevenue,
                sum(ProcurementCostTotal) as ProcurementCostTotal,
                sum(first_fees) as first_fees,
                sum(storage_fees) as storage_fees,
                sum(advertising_spend) as advertising_spend,
                sum(AdvEstimatedRebate) as AdvEstimatedRebate,
                sum(AdvNetExpenses) as AdvNetExpenses,
                sum(monthProfit) as monthProfit
            ')
            ->alias('a')
            ->where($where)
            ->order('id desc')
            ->find();

        $data = [
            'list' => $lists,
            'total' => $list->total(),
            'PanelSalesRevenue_total' => round($sum['PanelSalesRevenue'], 2),
            'refund_subtotal_total' => round($sum['refund_subtotal'], 2),
            'PlatformSettlementFee_total' => round($sum['PlatformSettlementFee'], 2),
            'BrushSale_total' => round($sum['BrushSale'], 2),
            'NetSalesRevenue_total' => round($sum['NetSalesRevenue'], 2),
            'ProcurementCostTotal_total' => round($sum['ProcurementCostTotal'], 2),
            'first_fees_total' => round($sum['first_fees'], 2),
            'storage_fees_total' => round($sum['storage_fees'], 2),
            'advertising_spend_total' => round($sum['advertising_spend'], 2),
            'AdvEstimatedRebate_total' => round($sum['AdvEstimatedRebate'], 2),
            'AdvNetExpenses_total' => round($sum['AdvNetExpenses'], 2),
            'monthProfit_total' => round($sum['monthProfit'], 2),
        ];
        return $data;
    }


    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 计算口径跟MonthlyGrossProfitStatistics方法不一样
     * @author zhangheng
     */
    public function ReportMonthlyProfit($params = [])
    {
        $shops = Db::name('shops')->select()->toarray();
        foreach ($shops as $k=>$v) {
            $params['shop_id'] = $v['id'];
            $this->ReportMonthlyProfitlist($params);
        }
        return true;
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 计算口径跟MonthlyGrossProfitStatistics方法不一样
     * @author zhangheng
     */
    public function ReportMonthlyProfitlist($params = [])
    {
        $date = $params['date'] ?? "";
        $shop_id = $params['shop_id'] ?? 1;
        if (!$date) {
            $thismonth = date('m')-1;
            $thisyear  = date('Y');
            $times = get_month_time($thisyear,$thismonth);
        }else{
            $date  = explode('-',$date);
            $times = get_month_time($date[0],$date[1]);
        }
        
        $start_time = $times['first'];
        $end_time   = $times['last'];

        $where = [];
        $where[] = ['a.createtime', '>=', $start_time];
        $where[] = ['a.createtime', '<=', $end_time];
        $where[] = ['a.shop_id', '=', $shop_id];
        
        $lists = Db::name('order_freeze')
            ->field('
                COUNT(a.id) as count,
                a.shop_id,
                sum(total_amount) as total_amount,
                sum(shipping_fee_platform_discount) as shipping_fee_platform_discount,
                sum(platform_discount) as platform_discount,
                sum(tax) as tax,
                b.name as shop_name,
                sum(shipping_fee) as shipping_fee
            ')
            ->alias('a')
            ->join('ba_shops b', 'b.id = a.shop_id', 'left')
            ->where($where)
            
            ->where(function ($query) use ($end_time) {  
                $query->where('a.status', "CANCELLED")  
                    ->where('a.cancel_time', ">=", $end_time + 16*3600);  
                $query->whereor('a.status', "<>", "CANCELLED");
            })  

            ->find();
            
        //计算其他数据
        $exchange = Db::name('exchange')
            ->where('date',date('Y-m',$start_time))
            ->order('date desc')
            ->find();

        $DailyEstimateLogic = new DailyEstimateLogic();
        if(!empty($lists)){ 
            if(empty($lists['shop_id'])){
                $lists['shop_id'] = $shop_id;
                $shops = Db::name('shops')->where('id',$shop_id)->find();
                if(!empty($shops)){
                    $lists['shop_name'] = $shops['name'];
                }
            }
            
            //面板广告费
            $where_campaign = [];
            $where_campaign[] = ['a.stat_time', '>=', $start_time];
            $where_campaign[] = ['a.stat_time', '<=', $end_time];
            $where_campaign[] = ['a.spend', '>=', 0.001];
            //平台结算费
            $where_statement = [];
            $where_statement[] = ['a.statement_time', '>=', $start_time - 16*3600];
            $where_statement[] = ['a.statement_time', '<', $end_time - 16*3600];
                //平台结算费
                $referral_fee_amount = Db::name('order_statement')
                    ->field('
                        SUM(a.referral_fee_amount) as PlatformSettlementFee,
                        ROUND(SUM(a.customer_refund_amount), 2) as customer_refund_amount,ROUND(SUM(a.platform_discount_refund_amount), 2) as platform_discount_refund_amount
                    ')
                    ->alias('a')
                    ->join('ba_order d','a.order_id = d.order_id', 'left')
                    ->where($where_statement)
                    ->where('d.shop_id',$lists['shop_id'])  
                    ->find();
                    
                if(!empty($referral_fee_amount['PlatformSettlementFee'])){
                    //平台结算费
                    $lists['PlatformSettlementFee'] = number_format($referral_fee_amount['PlatformSettlementFee'], 2, '.', '');
                }else{
                    $lists['PlatformSettlementFee'] = 0;
                }
                
                //新的退款方式
                // MissMi-Bras
                // LET'S GIRL
                $shop_ids = [12,10];
                if(in_array($shop_id,$shop_ids)){
                    $referral_fee_amount = Db::name('order_statement')
                        ->field('
                            SUM(a.after_seller_discounts_subtotal_amount) as after_seller_discounts_subtotal_amount
                        ')
                        ->alias('a')
                        ->join('ba_order d','a.order_id = d.order_id', 'left')
                        ->where($where_statement)
                        ->where("a.customer_refund_amount","<>",0)
                        ->where('d.shop_id',$lists['shop_id'])  
                        ->find();
                    $lists['refund_subtotal'] = abs(number_format($referral_fee_amount['after_seller_discounts_subtotal_amount'], 2, '.', ''));
                }else{
                    //退款
                    $lists['refund_subtotal'] = abs(number_format($referral_fee_amount['customer_refund_amount'] + $referral_fee_amount['platform_discount_refund_amount'], 2, '.', ''));
                }

                //海外仓运费
                $lists['storage_fees'] = number_format((float) $lists['count'] * $exchange['storage_fees'], 2, '.', '');
                //刷单
                $brushlist = Db::name('order_freeze')  
                    ->field('  
                        COUNT(a.id) as count,  
                        SUM(a.total_amount) as total_amount,  
                        SUM(a.shipping_fee_platform_discount) as shipping_fee_platform_discount,  
                        SUM(a.platform_discount) as platform_discount,  
                        SUM(a.tax) as tax,  
                        (SUM(a.total_amount) + SUM(a.platform_discount) + SUM(a.shipping_fee_platform_discount) - SUM(a.tax)) as BrushSale  
                    ')  
                    ->alias('a')  
                    ->where($where)  

                    ->where(function ($query) use ($end_time) {  
                        $query->where('a.status', "CANCELLED")  
                            ->where('a.cancel_time', ">=", $end_time + 16*3600);  
                        $query->whereor('a.status', "<>", "CANCELLED");
                    })  

                    ->where('a.brush_status', '=', 'brush')  
                    ->where('a.shop_id',$lists['shop_id'])  
                    ->order('total_amount desc')  
                    ->find();  
                $lists['BrushSale'] = isset($brushlist['BrushSale']) ? number_format((float)$brushlist['BrushSale'], 2, '.', '') : 0;  
                //刷单量
                $lists['BrushCount'] = isset($brushlist['count']) ? $brushlist['count'] : 0;
                //实际订单量
                $lists['ActualOrderQuantity'] = (int)$lists['count'] - (int)$lists['BrushCount'];
                //采购成本
                $whereProcurementCost = [];
                $whereProcurementCost[] = ['b.createtime', '>=', $start_time];
                $whereProcurementCost[] = ['b.createtime', '<=', $end_time];
                $ProcurementCostTotalArr = $DailyEstimateLogic->ProcurementCostFreeze($whereProcurementCost,$lists['shop_id'],$end_time);
                $lists['ProcurementCostTotal'] = number_format($ProcurementCostTotalArr['ProcurementCostTotal'] / $exchange['proportion'], 2, '.', '');
                $lists['skunums'] = $ProcurementCostTotalArr['nums'];

                //头程
                $lists['first_fees'] = number_format((float) $lists['skunums'] * $exchange['first_fees'] / $exchange['proportion'], 2, '.', '');

                // 5月15号不计算运费折扣
                if($start_time >= 1714492800){
                    $lists['shipping_fee_platform_discount'] = 0;
                }

                //面板销售额
                $PanelSalesRevenue = number_format((float)$lists['total_amount'] + $lists['platform_discount'] + $lists['shipping_fee_platform_discount'] - $lists['tax'], 2, '.', '');
                $lists['PanelSalesRevenue'] = $PanelSalesRevenue;

                //销售收入净值
                $NetSalesRevenue = number_format((float)$lists['PanelSalesRevenue'] - $lists['refund_subtotal'] + $lists['PlatformSettlementFee'] - $lists['BrushSale'], 2, '.', '');
                $lists['NetSalesRevenue'] = $NetSalesRevenue;

                //面板广告费
                $advertising_spend = $DailyEstimateLogic->advertising_campaign($where_campaign,$lists['shop_id']);
                $lists['advertising_spend'] = $advertising_spend;
                //广告费预估返点(10%)
                $lists['AdvEstimatedRebate'] = number_format($lists['advertising_spend'] * 0.1, 2, '.', '');
                //广告净花费
                $lists['AdvNetExpenses'] = number_format($lists['advertising_spend'] * 0.9, 2, '.', '');
                
                //ROI
                if(!empty($lists['PanelSalesRevenue']) && !empty($lists['advertising_spend'])){
                    $lists['roi'] = number_format((float)$lists['PanelSalesRevenue'] / $lists['advertising_spend'], 2, '.', '');
                }else{
                    $lists['roi'] = 0;
                }

                //每单平均价
                if(!empty($lists['PanelSalesRevenue']) && !empty($lists['count'])){
                    $lists['AveragePricePerOrder'] = number_format((float)$lists['PanelSalesRevenue'] / $lists['count'], 2, '.', '');
                }else{
                    $lists['AveragePricePerOrder'] = 0;
                }

                //实际盈利
                $lists['monthProfit'] = number_format((float)$lists['NetSalesRevenue'] - $lists['AdvNetExpenses'] - $lists['storage_fees'] - $lists['first_fees'] - $lists['ProcurementCostTotal'], 2, '.', '');
                $lists['date'] = date('Y-m',$start_time);
                $lists['time'] = $start_time;
            //入库
            $report_monthly_profit = Db::name('report_monthly_profit')
                ->where('time',$lists['time'])
                ->where('shop_id',$lists['shop_id'])
                ->find();
            if(!empty($report_monthly_profit)){
                Db::name('report_monthly_profit')
                    ->where('time',$lists['time'])
                    ->where('shop_id',$lists['shop_id'])
                    ->update($lists);
            }else{
                Db::name('report_monthly_profit')
                ->insert($lists);
            }
        }
        return $lists;
    }


    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 计算口径跟MonthlyGrossProfitStatistics方法不一样
     * @author zhangheng
     */
    public function getShopMonthIndexMonthInfo_refund($start_time,$end_time,$shop_id)
    {
        // 平台结算费
        $where_statement = [];
        $where_statement[] = ['a.statement_time', '>=', $start_time - 16*3600];
        $where_statement[] = ['a.statement_time', '<', $end_time - 16*3600];
        $where_statement[] = ['d.shop_id', '=', $shop_id];
        $lists = Db::name('order_statement')
            ->field('
                ROUND(SUM(a.referral_fee_amount), 2) as referral_fee_amount, 
                ROUND(SUM(a.customer_refund_amount), 2) as customer_refund_amount,
                ROUND(SUM(a.platform_discount_refund_amount), 2) as platform_discount_refund_amount,
                ROUND(SUM(a.after_seller_discounts_subtotal_amount), 2) as after_seller_discounts_subtotal_amount,
                a.order_id
            ')
            ->alias('a')
            ->join('ba_order d','a.order_id = d.order_id', 'left')
            ->where($where_statement)
            ->group("a.order_id")
            ->select()->toarray();
        
        return $lists;
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 计算口径跟MonthlyGrossProfitStatistics方法不一样
     * @author zhangheng
     */
    public function getShopMonthIndexMonthInfo_order($start_time,$end_time,$shop_id)
    {
        // 平台结算费
        $where = [];
        $where[] = ['createtime', '>=', $start_time];
        $where[] = ['createtime', '<', $end_time];
        $where[] = ['shop_id', '=', $shop_id];
        $lists = Db::name('order_freeze')
            ->field('
                order_id,
                total_amount,
                shipping_fee_platform_discount,
                platform_discount,
                tax,
                status,
                DATE_FORMAT(FROM_UNIXTIME(cancel_time), "%Y-%m-%d %H:%i:%s") as cancel_time  
            ')
            ->where($where)

            ->where(function ($query) use ($end_time) {  
                $query->where('status', "CANCELLED")  
                    ->where('cancel_time', ">=", $end_time);  
                $query->whereor('status', "<>", "CANCELLED");
            })  
            
            ->select()->toarray();
        return $lists;
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $shop_id = $params['shop_id'] ?? "";
        $date    = $params['date'] ?? '';
        $limit   = $params['limit'] ?? 20;
        $data = $this->getShopMonthIndexMonth($date,$shop_id,$limit);

        $header[] = ['店铺', 'shop_name'];
        $header[] = ['销售收入', 'PanelSalesRevenue'];
        $header[] = ['退款总额', 'refund_subtotal'];
        $header[] = ['平台结算费', 'PlatformSettlementFee'];
        $header[] = ['刷单金额', 'BrushSale'];
        $header[] = ['销售收入净值', 'NetSalesRevenue'];
        $header[] = ['采购成本', 'ProcurementCostTotal'];
        $header[] = ['头程分摊', 'first_fees'];
        $header[] = ['海外仓运费', 'storage_fees'];
        $header[] = ['广告费账单', 'advertising_spend'];
        $header[] = ['广告费预估返点(10%)', 'AdvEstimatedRebate'];
        $header[] = ['广告净花费', 'AdvNetExpenses'];
        $header[] = ['利润净值', 'monthProfit'];
        $header[] = ['sku总量计数', 'skunums'];
        $header[] = ['账面订单量', 'count'];
        $header[] = ['刷单量', 'BrushCount'];
        $header[] = ['实际订单量', 'ActualOrderQuantity'];
        $header[] = ['每单平均价', 'AveragePricePerOrder'];

        $fileName = "MonthlyPerformance";
        $list = isset($data['list']) ? $data['list'] : [];
        return Excel::exportData($list, $header, $fileName, 'xlsx');
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function exportinfo($params = [])
    {
        $id = $params['id'] ?? "";
        if(!empty($id)){
            $report_monthly_profit = Db::name('report_monthly_profit')
                ->where('id',$id)
                ->find();
            $start_time = $report_monthly_profit['time'];
            $end_time = strtotime('first day of next month', $start_time);  

            //结算跟退款
            // $data = $this->getShopMonthIndexMonthInfo_refund($start_time,$end_time,$report_monthly_profit['shop_id']);
            // $header = [];
            // $header[] = ['订单号', 'order_id'];
            // $header[] = ['退款', 'customer_refund_amount'];
            // $header[] = ['平台退款', 'platform_discount_refund_amount'];
            // $header[] = ['结算费', 'referral_fee_amount'];
            // $header[] = ['新的退款金额', 'after_seller_discounts_subtotal_amount'];

            //订单明细
            $data = $this->getShopMonthIndexMonthInfo_order($start_time,$end_time,$report_monthly_profit['shop_id']);
            $header = [];
            $header[] = ['订单号', 'order_id'];
            $header[] = ['销售额', 'total_amount'];
            $header[] = ['运费折扣', 'shipping_fee_platform_discount'];
            $header[] = ['平台折扣', 'platform_discount'];
            $header[] = ['税费', 'tax'];
            $header[] = ['订单状态', 'status'];
            $header[] = ['关闭时间', 'cancel_time'];

            //SKU明细
            // $where = [];
            // $where[] = ['b.createtime', '>=', $start_time];
            // $where[] = ['b.createtime', '<=', $end_time];
            // $where[] = ['b.shop_id', '=', $report_monthly_profit['shop_id']];
            // $data = Db::name('order_info')    
            //     ->field('a.order_id,a.info_id,a.seller_sku,a.num')    
            //     ->alias('a')    
            //     ->join('ba_order_freeze b', 'b.order_id = a.order_id', 'left')   
            //     ->where($where) 
            //     ->where(function ($query) use ($end_time) {  
            //         $query->where('b.status', "CANCELLED")  
            //             ->where('b.cancel_time', ">=", $end_time + 16*3600);  
            //         $query->whereor('b.status', "<>", "CANCELLED");
            //     })
            //     ->order('num desc') 
            //     ->select()->toArray();
            // $header = [];
            // $header[] = ['订单号', 'order_id'];
            // $header[] = ['info_id', 'info_id'];
            // $header[] = ['SKU', 'seller_sku'];
            // $header[] = ['件数', 'num'];
                
            $fileName = "exportinfo";
            return Excel::exportData($data, $header, $fileName, 'xlsx');
        }else{
            return json(DefaultResponse(200, true, '更新成功'));
        } 
    }

}